# -*- coding: iso-8859-1 -*-
"""
@newfield project: Project 
@project: yatter
@newfield description: Description 
@description: Twitter Client, based on Python 2.5

@Author: U{Andreas del Galdo<mailto:andreas.delgaldo@mac.com>}
@summary: Handles the access to the database, stores and loads the data out of it. 
          Tweets and Userdata is cached here.
"""

import sqlite3
import logging
import os

def connectToDatabase(db):
    try:
        logging.debug("storage.store_sqlite.connectToDatabase()")
        if (os.path.exists(db)):
            createTables = False
        else:
            createTables = True
            
        db_connection = sqlite3.connect(db)
        db_cursor = db_connection.cursor()
        
        if (createTables):
            logging.debug("storage.store_sqlite.connectToDatabase()::createTables(tweets)")
            db_cursor.execute("""CREATE TABLE public_tweets(tweet_id TEXT, user_id TEXT, created_at TEXT, tweet_text TEXT, source TEXT, trucated TEXT, in_reply_to_status_id TEXT, in_reply_to_user_id TEXT, favorited INTEGER)""")
            db_cursor.execute("""CREATE TABLE private_tweets(tweet_id TEXT, user_id TEXT, created_at TEXT, tweet_text TEXT, source TEXT, trucated TEXT, in_reply_to_status_id TEXT, in_reply_to_user_id TEXT, favorited INTEGER)""")
            db_cursor.execute("""CREATE TABLE reply_tweets(tweet_id TEXT, user_id TEXT, created_at TEXT, tweet_text TEXT, source TEXT, trucated TEXT, in_reply_to_status_id TEXT, in_reply_to_user_id TEXT, favorited INTEGER)""")
            db_cursor.execute("""CREATE TABLE direct_tweets(tweet_id TEXT, user_id TEXT, created_at TEXT, tweet_text TEXT, source TEXT, trucated TEXT, in_reply_to_status_id TEXT, in_reply_to_user_id TEXT, favorited INTEGER)""")

            logging.debug("storage.store_sqlite.connectToDatabase()::createTables(users)")
            db_cursor.execute("""CREATE TABLE public_users(user_id TEXT, name TEXT, screen_name TEXT, description TEXT, location TEXT, profile_image_url TEXT, url TEXT, protected TEXT, followers_count INTEGER)""")
            db_cursor.execute("""CREATE TABLE private_users(user_id TEXT, name TEXT, screen_name TEXT, description TEXT, location TEXT, profile_image_url TEXT, url TEXT, protected TEXT, followers_count INTEGER)""")

            logging.debug("storage.store_sqlite.connectToDatabase()::createTables(last_tweet)")
            db_cursor.execute("""CREATE TABLE last_tweet(tweet_id TEXT, user_id TEXT)""")

            logging.debug("storage.store_sqlite.connectToDatabase()::createTables(settings)")
            db_cursor.execute("""CREATE TABLE settings(tw_user TEXT, tw_pass TEXT, proxy_host TEXT, proxy_port TEXT, proxy_user TEXT, proxy_pass TEXT)""")
        return db_connection
    except:
        logging.error("storage.store_sqlite.connectToDatabase()::ERROR")
        return False


def insertToDatabase(db_connection,table,data):
    try:
        logging.debug("storage.store_sqlite.insertToDatabase()")
        db_cursor = db_connection.cursor()
        db_cursor.execute(data)
        db_connection.commit()
        return True
    except:
        logging.error("storage.store_sqlite.insertToDatabase()::ERROR")
        return False
    

def getFromDatabase(db_connection,table,data):
    try:
        logging.debug("storage.store_sqlite.getFromDatabase()")
        db_cursor = db_connection.cursor()
        db_cursor.execute(data)
        return db_cursor.row()
    except:
        logging.error("storage.store_sqlite.getFromDatabase()::ERROR")
        return False